#!/usr/bin/env bun

/**
 * SQLite 数据库查看工具
 * 使用方法：
 * bun run src/scripts/database-viewer.ts
 * bun run src/scripts/database-viewer.ts --table users
 * bun run src/scripts/database-viewer.ts --query "SELECT * FROM users WHERE role = 'admin'"
 * bun run src/scripts/database-viewer.ts --schema
 * bun run src/scripts/database-viewer.ts --stats
 */

import { Database } from 'bun:sqlite';
import { join } from 'path';

// 数据库路径
const DB_PATH = join(process.cwd(), 'database.sqlite');

// 颜色输出
const colors = {
  reset: '\x1b[0m',
  bright: '\x1b[1m',
  red: '\x1b[31m',
  green: '\x1b[32m',
  yellow: '\x1b[33m',
  blue: '\x1b[34m',
  magenta: '\x1b[35m',
  cyan: '\x1b[36m',
  white: '\x1b[37m',
};

function colorize(text: string, color: keyof typeof colors): string {
  return `${colors[color]}${text}${colors.reset}`;
}

function printHeader(title: string) {
  console.log('\n' + '='.repeat(60));
  console.log(colorize(title, 'cyan'));
  console.log('='.repeat(60));
}

function printTable(data: any[], title?: string) {
  if (title) {
    console.log(colorize(`\n📊 ${title}`, 'yellow'));
  }
  
  if (!data || data.length === 0) {
    console.log(colorize('  (无数据)', 'red'));
    return;
  }

  // 获取所有列名
  const columns = Object.keys(data[0]);
  
  // 计算每列的最大宽度
  const columnWidths = columns.map(col => {
    const maxDataWidth = Math.max(...data.map(row => 
      String(row[col] || '').length
    ));
    return Math.max(col.length, maxDataWidth, 8);
  });

  // 打印表头
  console.log('\n┌' + columnWidths.map(w => '─'.repeat(w + 2)).join('┬') + '┐');
  console.log('│ ' + columns.map((col, i) => 
    colorize(col.padEnd(columnWidths[i]), 'bright')
  ).join(' │ ') + ' │');
  console.log('├' + columnWidths.map(w => '─'.repeat(w + 2)).join('┼') + '┤');

  // 打印数据行
  data.forEach(row => {
    console.log('│ ' + columns.map((col, i) => {
      const value = String(row[col] || '');
      return value.padEnd(columnWidths[i]);
    }).join(' │ ') + ' │');
  });

  console.log('└' + columnWidths.map(w => '─'.repeat(w + 2)).join('┴') + '┘');
  console.log(colorize(`总计: ${data.length} 行`, 'green'));
}

function showSchema(db: Database) {
  printHeader('数据库结构');
  
  // 获取所有表
  const tables = db.query(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name NOT LIKE 'sqlite_%'
    ORDER BY name
  `).all() as { name: string }[];

  console.log(colorize(`\n📋 数据库表 (${tables.length} 个):`, 'yellow'));
  
  tables.forEach(table => {
    console.log(colorize(`\n🔸 ${table.name}`, 'cyan'));
    
    // 获取表结构
    const columns = db.query(`PRAGMA table_info(${table.name})`).all() as any[];
    
    console.log('  列信息:');
    columns.forEach(col => {
      const nullable = col.notnull ? colorize('NOT NULL', 'red') : colorize('NULL', 'green');
      const pk = col.pk ? colorize(' [PK]', 'yellow') : '';
      const defaultVal = col.dflt_value ? ` (默认: ${col.dflt_value})` : '';
      
      console.log(`    ${colorize(col.name, 'bright')}: ${col.type} ${nullable}${pk}${defaultVal}`);
    });

    // 获取行数
    const count = db.query(`SELECT COUNT(*) as count FROM ${table.name}`).get() as { count: number };
    console.log(`  ${colorize(`数据行数: ${count.count}`, 'green')}`);
  });
}

function showStats(db: Database) {
  printHeader('数据库统计');
  
  // 数据库文件信息
  const dbInfo = db.query('PRAGMA database_list').all() as any[];
  console.log(colorize('\n📁 数据库文件:', 'yellow'));
  dbInfo.forEach(info => {
    console.log(`  ${info.name}: ${info.file}`);
  });

  // 表统计
  const tables = db.query(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name NOT LIKE 'sqlite_%'
    ORDER BY name
  `).all() as { name: string }[];

  console.log(colorize('\n📊 表统计:', 'yellow'));
  const tableStats = tables.map(table => {
    const count = db.query(`SELECT COUNT(*) as count FROM ${table.name}`).get() as { count: number };
    return {
      表名: table.name,
      行数: count.count
    };
  });
  
  printTable(tableStats);

  // 数据库大小
  const sizeQuery = db.query('PRAGMA page_count').get() as { page_count: number };
  const pageSizeQuery = db.query('PRAGMA page_size').get() as { page_size: number };
  const sizeInBytes = sizeQuery.page_count * pageSizeQuery.page_size;
  const sizeInMB = (sizeInBytes / 1024 / 1024).toFixed(2);
  
  console.log(colorize(`\n💾 数据库大小: ${sizeInMB} MB`, 'green'));
}

function showTableData(db: Database, tableName: string, limit: number = 50) {
  printHeader(`表数据: ${tableName}`);
  
  try {
    // 检查表是否存在
    const tableExists = db.query(`
      SELECT name FROM sqlite_master 
      WHERE type='table' AND name = ?
    `).get(tableName);

    if (!tableExists) {
      console.log(colorize(`❌ 表 '${tableName}' 不存在`, 'red'));
      return;
    }

    // 获取数据
    const data = db.query(`SELECT * FROM ${tableName} LIMIT ${limit}`).all();
    printTable(data, `${tableName} (最多显示 ${limit} 行)`);
    
    // 显示总行数
    const totalCount = db.query(`SELECT COUNT(*) as count FROM ${tableName}`).get() as { count: number };
    if (totalCount.count > limit) {
      console.log(colorize(`\n💡 提示: 表中共有 ${totalCount.count} 行数据，仅显示前 ${limit} 行`, 'yellow'));
    }
  } catch (error) {
    console.log(colorize(`❌ 查询表 '${tableName}' 时出错: ${error}`, 'red'));
  }
}

function executeQuery(db: Database, query: string) {
  printHeader('自定义查询结果');
  
  try {
    console.log(colorize(`🔍 执行查询: ${query}`, 'blue'));
    
    const result = db.query(query).all();
    printTable(result);
  } catch (error) {
    console.log(colorize(`❌ 查询执行失败: ${error}`, 'red'));
  }
}

function showHelp() {
  printHeader('SQLite 数据库查看工具');
  
  console.log(colorize('\n📖 使用方法:', 'yellow'));
  console.log('  bun run src/scripts/database-viewer.ts                    # 显示所有表的数据');
  console.log('  bun run src/scripts/database-viewer.ts --schema           # 显示数据库结构');
  console.log('  bun run src/scripts/database-viewer.ts --stats            # 显示数据库统计');
  console.log('  bun run src/scripts/database-viewer.ts --table users      # 显示指定表的数据');
  console.log('  bun run src/scripts/database-viewer.ts --query "SQL"      # 执行自定义SQL查询');
  console.log('  bun run src/scripts/database-viewer.ts --help             # 显示帮助信息');
  
  console.log(colorize('\n💡 示例:', 'yellow'));
  console.log('  bun run src/scripts/database-viewer.ts --table banners');
  console.log('  bun run src/scripts/database-viewer.ts --query "SELECT * FROM users WHERE role = \'admin\'"');
  console.log('  bun run src/scripts/database-viewer.ts --query "SELECT COUNT(*) as total FROM products"');
}

async function main() {
  const args = process.argv.slice(2);
  
  // 显示帮助
  if (args.includes('--help') || args.includes('-h')) {
    showHelp();
    return;
  }

  try {
    // 连接数据库
    const db = new Database(DB_PATH);
    
    console.log(colorize(`🔗 连接到数据库: ${DB_PATH}`, 'green'));

    // 解析参数
    if (args.includes('--schema')) {
      showSchema(db);
    } else if (args.includes('--stats')) {
      showStats(db);
    } else if (args.includes('--table')) {
      const tableIndex = args.indexOf('--table');
      const tableName = args[tableIndex + 1];
      if (!tableName) {
        console.log(colorize('❌ 请指定表名', 'red'));
        return;
      }
      showTableData(db, tableName);
    } else if (args.includes('--query')) {
      const queryIndex = args.indexOf('--query');
      const query = args[queryIndex + 1];
      if (!query) {
        console.log(colorize('❌ 请指定SQL查询', 'red'));
        return;
      }
      executeQuery(db, query);
    } else {
      // 默认显示所有表的数据
      showSchema(db);
      showStats(db);
      
      // 显示主要表的数据
      const mainTables = ['users', 'banners', 'products', 'cases', 'settings'];
      for (const table of mainTables) {
        try {
          showTableData(db, table, 10);
        } catch (error) {
          // 表可能不存在，跳过
        }
      }
    }

    db.close();
    console.log(colorize('\n✅ 数据库连接已关闭', 'green'));
    
  } catch (error) {
    console.log(colorize(`❌ 数据库操作失败: ${error}`, 'red'));
    console.log(colorize(`💡 请确保数据库文件存在: ${DB_PATH}`, 'yellow'));
  }
}

// 运行主函数
main().catch(console.error);